package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import modelo.Persona;
import modelo.Cronograma;
import util.ConexionBD;
import excepcion.DAOExcepcion;

public class ProgramacionDAO extends BaseDAO{
	

	public void reprogramar(Cronograma programa) throws DAOExcepcion {
		System.out.println("CanchaDAO: insertar(Cancha vo)");
		String query = "INSERT INTO cancha(caracteristicas, Local_idLocal,Local_Persona_IdPersona) VALUES (?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, programa.getCodigo());
			stmt.setString(2, programa.getFecha());
			stmt.setDouble(3, programa.getTarifa());
			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}
	
	public void insertprogramar(Cronograma pro) throws DAOExcepcion {
		System.out.println("CanchaDAO: insertar(programa pro)");
		String query = "INSERT INTO programacion (fecha, hora, tarifa, promociones) VALUES ( ?, ?, ?, ?)";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, pro.getFecha());
			stmt.setString(2, pro.getHora());
			stmt.setDouble(3, pro.getTarifa());
			stmt.setString(4, pro.getPromociones());
		
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}
	
	public void insertProgramaCancha(int cancha) throws DAOExcepcion {
		System.out.println("ProgramacionDAO: insertProgramaCancha(programacion cancha)");
		

		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
		
			String q = "SELECT max(idCronograma) as last from Cronograma";	
			int idCronograma=0;
			stmt = con.prepareStatement(q);
			rs = stmt.executeQuery();
			while (rs.next()) {
				idCronograma = rs.getInt("last");
			}
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			
			String query = "INSERT INTO Programacion (Cronograma_idCronograma, Cancha_idCancha) VALUES ( ?, ?)";
		
			stmt = con.prepareStatement(query);
			stmt.setInt(1, idCronograma);
			stmt.setInt(2, cancha);
			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}
	
	public Collection<Cronograma> listarProgramacion() throws DAOExcepcion {
		System.out.println("PersonaDAO: listarPersona()");
		Collection<Cronograma> c = new ArrayList<Cronograma>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			String query = "select fecha, hora, tarifa, promociones, caracteristicas from programacion p inner join cronograma cr on p.idprogramacion = cr.programacion_idprogramacion inner join cancha c on cr.cancha_idcancha=c.idcancha order by fecha desc, caracteristicas asc, hora desc";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Cronograma pe = new Cronograma();
				pe.setFecha(rs.getString("fecha"));
				pe.setHora(rs.getString("hora"));
				pe.setPromociones(rs.getString("promociones"));
				pe.setCanchaCaracteriticas(rs.getString("caracteristicas"));
				pe.setTarifa(rs.getDouble(("tarifa")));
				c.add(pe);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return c;
	}

}